namespace CISLib
{
    using System;
    using System.Collections;
    using System.Data;
    using System.Data.SqlClient;

    public class ManageArea
    {
        public static SqlDataReader GetArea()
        {
            string strSQL = "Select * from TB_Area";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static Area[] GetAreaAryViaId(Guid userId)
        {
            SqlDataReader MyReader = null;
            ArrayList areaList = new ArrayList();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(userId, "UserId", SqlDbType.UniqueIdentifier);
            string strSQL = "Select TB_UserArea.AreaId from TB_UserArea left outer join tb_area on TB_UserArea.areaid = tb_area.areaid where TB_UserArea.UserId = @UserId order by tb_area.areaname";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            while (MyReader.Read())
            {
                Area area = new Area();
                area = GetAreaViaId(MyReader.GetGuid(0));
                areaList.Add(area);
            }
            MyDB.DBClose();
            return (Area[]) areaList.ToArray(typeof(Area));
        }

        public static Area GetAreaViaId(Guid id)
        {
            SqlDataReader MyReader = null;
            Area area = new Area();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_Area where AreaId = @AreaId";
            MyDB.AddParameters(id, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                area.AreaId = MyReader.GetGuid(0);
                area.AreaName = MyReader.GetString(1);
                area.AreaSCName = MyReader.GetString(2);
            }
            MyDB.DBClose();
            return area;
        }

        public static void InsertNewArea(string name, string scName)
        {
            Guid id = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "AreaName", SqlDbType.VarChar);
            MyDB.AddParameters(scName, "AreaSCName", SqlDbType.VarChar);
            MyDB.AddParameters(id, "AreaId", SqlDbType.UniqueIdentifier);
            string strSQL = "Insert into TB_Area (AreaName,AreaSCName,AreaId) values (@AreaName,@AreaSCName,@AreaId)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static bool IsExistSameAreaName(string name)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "AreaName", SqlDbType.VarChar);
            string strSQL = "select * from TB_Area where AreaName = @AreaName";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static void RemoveAreaViaId(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "AreaId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_Area where AreaId = @AreaId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateArea(string name, string scName, Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "AreaName", SqlDbType.VarChar);
            MyDB.AddParameters(scName, "AreaSCName", SqlDbType.VarChar);
            MyDB.AddParameters(id, "AreaId", SqlDbType.UniqueIdentifier);
            string strSQL = "Update TB_Area set AreaName = @AreaName,AreaSCName = @AreaSCName where AreaId = @AreaId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }
    }
}

